package com.qf.index;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.json.JSONUtil;
import com.alibaba.fastjson.JSONObject;
import lombok.Data;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;

public class WosDown {

    /**
     * 本程序的功能是，整合WOS的检索式，按设置的最大检索式长度、最大检索数量，让其检索的次数尽可能少，然后开始下载UT号。
     * 贾新志 2025.03.28
     */

    public static void main(String[] args) throws SQLException {

        String newDataName = "scholar_25_02.";
        String downWosTable = newDataName + "down_wos_table";
        String wordsNumberTable = newDataName + "wos_down_words_number";
        String jcrJour2024Table = newDataName + "jcr_jour_detail_2019_2023_back";
        String jcrJourDetailTable = newDataName + "jcr_journal_impact_factor_1997_2023";
        String utArticleTable = newDataName + "spider_tb_true_92007022_1125";

        getDownWosTable(jcrJour2024Table, jcrJourDetailTable, utArticleTable, downWosTable);
        insertWordsNumberTable(downWosTable, "id", "journal_list`,`sum", wordsNumberTable);

    }

    public static void insertWordsNumberTable(String downWosTable, String autoId, String field, String wordsNumberTable) throws SQLException {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        int onePageNum = 1000;
        List<Entity> tableNum = Db.use().query("select count(1) as count from " + downWosTable);
        Map<String, Integer> wordAndSumMap = new HashMap<>();
        if (tableNum.get(0).getInt("count") > 0) {
            int tableMax = Db.use().queryNumber("select max(" + autoId + ") from " + downWosTable).intValue();
            Collection<Integer> tableList = new ArrayList<>();
            NumberUtil.appendRange(0, tableMax + onePageNum, onePageNum, tableList);
            for (Integer startId : tableList) {
                try {
                    List<Entity> tableData = Db.use().query("select `" + autoId + "`,`" + field + "` from " + downWosTable + " where " + autoId + " between " + startId + " and " + (startId + onePageNum - 1));
                    System.out.println("\033[31;1m" + format.format(new Date()) + " 读表现在开始：" + startId + " 表名：" + downWosTable + Utils.printMemoryStr() + "\033[0m");
                    for (Entity entry : tableData) {
                        String journals = entry.getStr("journal_list");
                        Integer sum = entry.getInt("sum");
                        sum = null == sum || sum == 0 ? 100 : sum;
                        List<String> wordList = new ArrayList<>();
                        if (null != journals && journals.contains("\"]")) {
                            List<String> journalList = JSONObject.parseArray(journals, String.class);
                            for (String journal : journalList) {
                                String s = journal.toUpperCase();
                                if (s.length() == 9 && s.charAt(4) == '-' && !s.contains("****")) {
                                    wordList.add("IS=" + s);
                                } else if (s.length() > 2 && !s.contains("****")) {
                                    s = (s.contains(" NOT ") || s.contains(" AND ") || s.contains(" OR ") || s.contains(" NEAR ") || s.contains(" SAME ") ||
                                            s.endsWith(" NOT") || s.endsWith(" AND") || s.endsWith(" OR") || s.endsWith(" NEAR") || s.endsWith(" SAME") ||
                                            s.startsWith("NOT ") || s.startsWith("AND ") || s.startsWith("OR ") || s.startsWith("NEAR ") || s.startsWith("SAME ") || s.endsWith("-")) ? ("\"" + s + "\"") : s;
                                    wordList.add("SO=" + s);
                                }
                            }
                        }
                        wordAndSumMap.put(CollUtil.join(wordList, " OR "), sum);
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        int maxSum = 30000; // 系统设置的最大的检索结果数量
        int maxWords = 4000; // 系统设置的最大的检索词的长度
        int threshold = 2000; // 分界点，是选择一大一小，还是选择一大，其它都是小的分界点值，默认按2000。
        List<WordsNumber> wordsNumberList = getWordsNumberList(maxSum, maxWords, threshold, wordAndSumMap);
        System.out.println(format.format(new Date()) + " threshold: " + threshold + " wordsNumbers.size():" + wordsNumberList.size());

        Db.use().execute("truncate table " + wordsNumberTable);
        for (WordsNumber wordsNumber : wordsNumberList) {
            String words = wordsNumber.getWords();
            int number = wordsNumber.getNumber();
            try {
                Db.use().insert(Entity.create(wordsNumberTable).set("words", words).set("words_len", words.length()).set("is_yearly", words.contains(" AND (PY=2025 OR PY=2024") ? 0 : 1).set("number", number));
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static List<WordsNumber> getWordsNumberList(int maxSum, int maxWords, int threshold, Map<String, Integer> wordAndSumMap) {
        List<WordsNumber> wordsNumberList = new ArrayList<>();
        while (!wordAndSumMap.isEmpty()) {
            StringBuilder words = new StringBuilder(); // 本次的检索词的组合
            int numberSum = 0; // 本次的累加的数量
            int firstMax = -1;
            while (true) {
                int max = Integer.MIN_VALUE;
                int min = Integer.MAX_VALUE;
                String maxWord = "";
                String minWord = "";
                for (Map.Entry<String, Integer> entry : wordAndSumMap.entrySet()) {
                    String word = entry.getKey();
                    Integer number = entry.getValue();
                    if (number > max) {
                        max = number;
                        maxWord = word;
                    }
                    if (min > number) {
                        min = number;
                        minWord = word;
                    }
                }
                firstMax = firstMax == -1 ? max : firstMax;
                if (words.isEmpty()) {
                    words = new StringBuilder(maxWord + " OR " + minWord);
                    wordAndSumMap.remove(maxWord);
                    wordAndSumMap.remove(minWord);
                    numberSum = max + min;
                } else if (firstMax > maxSum) {
                    // 对于单刊大于3万的，全部采用按年下载
                    if (words.length() > maxWords || wordAndSumMap.isEmpty()) {
                        addWordsNumberList(wordsNumberList, numberSum, "(" + words + ")" + " AND PY=2019");
                        addWordsNumberList(wordsNumberList, numberSum, "(" + words + ")" + " AND PY=2020");
                        addWordsNumberList(wordsNumberList, numberSum, "(" + words + ")" + " AND PY=2021");
                        addWordsNumberList(wordsNumberList, numberSum, "(" + words + ")" + " AND PY=2022");
                        addWordsNumberList(wordsNumberList, numberSum, "(" + words + ")" + " AND PY=2023");
                        addWordsNumberList(wordsNumberList, numberSum, "(" + words + ")" + " AND PY=2024");
                        addWordsNumberList(wordsNumberList, numberSum, "(" + words + ")" + " AND PY=2025");
                        break;
                    } else {
                        words.append(" OR ").append(minWord);
                        numberSum += min;
                        wordAndSumMap.remove(minWord);
                    }
                } else if (numberSum > maxSum || words.length() > maxWords || wordAndSumMap.isEmpty()) {
                    addWordsNumberList(wordsNumberList, numberSum, "(" + words + ")" + " AND (PY=2025 OR PY=2024 OR PY=2023 OR PY=2022 OR PY=2021 OR PY=2020 OR PY=2019)");
                    break;
                } else {
                    if (max > threshold) {
                        words.append(" OR ").append(minWord);
                        numberSum += min;
                        wordAndSumMap.remove(minWord);
                    } else {
                        words.append(" OR ").append(maxWord).append(" OR ").append(minWord);
                        wordAndSumMap.remove(maxWord);
                        wordAndSumMap.remove(minWord);
                        numberSum += (max + min);
                    }
                }
            }
        }
        return wordsNumberList;
    }

    public static void addWordsNumberList(List<WordsNumber> wordsNumberList, int numberSum, String words) {
        WordsNumber wordsNumber = new WordsNumber();
        wordsNumber.setNumber(numberSum);
        wordsNumber.setWords(words);
        wordsNumberList.add(wordsNumber);
    }

    @Data
    //最终结果的实体类
    public static class WordsNumber {
        private String words = "";
        private int number = 0;
    }

    public static void insertDownWosTable(String utArticleTable, String autoId, String field, Set<String> equalSet, String downWosTable, String limit) throws SQLException {
        String spl = "ĺļ";
        List<Set<String>> unionFindList = mergeJournal.unionFind(equalSet, spl);
        Map<Integer, Set<String>> orderAndEqualSetMap = new HashMap<>();
        Map<String, Integer> journalAndOrderMap = new HashMap<>();
        for (int i = 0; i < unionFindList.size(); i++) {
            Set<String> set = unionFindList.get(i);
            orderAndEqualSetMap.put(i, set);
            for (String journal : set) {
                journalAndOrderMap.put(journal, i);
            }
        }
        Map<Integer, Set<Integer>> orderAndPidSetMap = new HashMap<>();
        int onePageNum = 1000;
        List<Entity> tableNum = Db.use().query("select count(1) as count from " + utArticleTable);
        if (tableNum.get(0).getInt("count") > 0) {
            int tableMax = Db.use().queryNumber("select max(" + autoId + ") from " + utArticleTable).intValue();
            Collection<Integer> tableList = new ArrayList<>();
            NumberUtil.appendRange(0, tableMax + onePageNum, onePageNum, tableList);
            for (Integer startId : tableList) {
                List<Entity> tableData = Db.use().query("select `" + autoId + "`,`" + field + "` from " + utArticleTable + " where " + autoId + " between " + startId + " and " + (startId + onePageNum - 1) + limit);
                for (Entity entry : tableData) {
                    String abbr = Utils.getStrField(entry, "J9").toLowerCase().trim();
                    String issn = Utils.getStrField(entry, "SN").toLowerCase().trim();
                    String eissn = Utils.getStrField(entry, "EI").toLowerCase().trim();
                    int pid = Utils.getIntField(entry, autoId);
                    int year = Utils.getStrToInt(entry, "PY");
                    if (pid > 0 && year > 2018) {
                        if (abbr.length() > 2 && journalAndOrderMap.containsKey(abbr)) {
                            Utils.putIntSetIntMap(orderAndPidSetMap, journalAndOrderMap.get(abbr), pid);
                        }
                        if (issn.length() == 9 && issn.charAt(4) == '-' && !issn.contains("*") && journalAndOrderMap.containsKey(issn)) {
                            Utils.putIntSetIntMap(orderAndPidSetMap, journalAndOrderMap.get(issn), pid);
                        }
                        if (eissn.length() == 9 && eissn.charAt(4) == '-' && !eissn.contains("*") && journalAndOrderMap.containsKey(eissn)) {
                            Utils.putIntSetIntMap(orderAndPidSetMap, journalAndOrderMap.get(eissn), pid);
                        }
                    }
                }
            }
        }
        Db.use().execute("truncate table " + downWosTable);
        for (Map.Entry<Integer, Set<String>> entry : orderAndEqualSetMap.entrySet()) {
            Integer order = entry.getKey();
            Set<String> journalSet = entry.getValue();
            Set<Integer> pidSet = orderAndPidSetMap.containsKey(order) ? orderAndPidSetMap.get(order) : new HashSet<>();
            try {
                Db.use().insert(Entity.create(downWosTable).set("journal_list", JSONUtil.toJsonStr(journalSet)).set("sum", pidSet.size()).set("order", order).set("pid_list", JSONUtil.toJsonStr(pidSet)).set("journal_sum", journalSet.size()));
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void readUtArticleTable(String utArticleTable, String autoId, String field, Set<String> equalSet, String limit) throws SQLException {
        int onePageNum = 1000;
        List<Entity> tableNum = Db.use().query("select count(1) as count from " + utArticleTable);
        String spl = "ĺļ";
        if (tableNum.get(0).getInt("count") > 0) {
            int tableMax = Db.use().queryNumber("select max(" + autoId + ") from " + utArticleTable).intValue();
            Collection<Integer> tableList = new ArrayList<>();
            NumberUtil.appendRange(0, tableMax + onePageNum, onePageNum, tableList);
            for (Integer startId : tableList) {
                List<Entity> tableData = Db.use().query("select `" + autoId + "`,`" + field + "` from " + utArticleTable + " where " + autoId + " between " + startId + " and " + (startId + onePageNum - 1) + limit);
                for (Entity entry : tableData) {
                    String abbr = Utils.getStrField(entry, "J9").toLowerCase().trim();
                    String issn = Utils.getStrField(entry, "SN").toLowerCase().trim();
                    String eissn = Utils.getStrField(entry, "EI").toLowerCase().trim();
                    Set<String> set = new HashSet<>();
                    if (abbr.length() > 2) {
                        set.add(abbr);
                    }
                    if (issn.length() == 9 && issn.charAt(4) == '-' && !issn.contains("*")) {
                        set.add(issn);
                    }
                    if (eissn.length() == 9 && eissn.charAt(4) == '-' && !eissn.contains("*")) {
                        set.add(eissn);
                    }
                    if (!set.isEmpty()) {
                        equalSet.add(CollUtil.join(set, spl));
                    }
                }
            }
        }
    }

    public static void readJcrJourDetailTable(String jcrJourDetailTable, String autoId, String field, Set<String> equalSet, String limit) throws SQLException {
        int onePageNum = 1000;
        List<Entity> tableNum = Db.use().query("select count(1) as count from " + jcrJourDetailTable);
        String spl = "ĺļ";
        if (tableNum.get(0).getInt("count") > 0) {
            int tableMax = Db.use().queryNumber("select max(" + autoId + ") from " + jcrJourDetailTable).intValue();
            Collection<Integer> tableList = new ArrayList<>();
            NumberUtil.appendRange(0, tableMax + onePageNum, onePageNum, tableList);
            for (Integer startId : tableList) {
                List<Entity> tableData = Db.use().query("select `" + autoId + "`,`" + field + "` from " + jcrJourDetailTable + " where " + autoId + " between " + startId + " and " + (startId + onePageNum - 1) + limit);
                for (Entity entry : tableData) {
                    String full = Utils.getStrField(entry, "full_journal_title").toLowerCase().trim();
                    String abbr = Utils.getStrField(entry, "jcr_abbreviated_title").toLowerCase().trim();
                    String issn = Utils.getStrField(entry, "issn").toLowerCase().trim();
                    Set<String> set = new HashSet<>();
                    if (full.length() > 2) {
                        set.add(abbr);
                    }
                    if (abbr.length() > 2) {
                        set.add(abbr);
                    }
                    if (issn.length() == 9 && issn.charAt(4) == '-' && !issn.contains("*")) {
                        set.add(issn);
                    }
                    if (!set.isEmpty()) {
                        equalSet.add(CollUtil.join(set, spl));
                    }
                }
            }
        }
    }

    public static void readJcrJour2024Table(String jcrJour2024Table, String autoId, String field, Set<String> equalSet, String limit) throws SQLException {
        int onePageNum = 1000;
        List<Entity> tableNum = Db.use().query("select count(1) as count from " + jcrJour2024Table);
        String spl = "ĺļ";
        if (tableNum.get(0).getInt("count") > 0) {
            int tableMax = Db.use().queryNumber("select max(" + autoId + ") from " + jcrJour2024Table).intValue();
            Collection<Integer> tableList = new ArrayList<>();
            NumberUtil.appendRange(0, tableMax + onePageNum, onePageNum, tableList);
            for (Integer startId : tableList) {
                List<Entity> tableData = Db.use().query("select `" + autoId + "`,`" + field + "` from " + jcrJour2024Table + " where " + autoId + " between " + startId + " and " + (startId + onePageNum - 1) + limit);
                for (Entity entry : tableData) {
                    String full = Utils.getStrField(entry, "journal_name").toLowerCase().trim();
                    String abbr = Utils.getStrField(entry, "abbr_journal").toLowerCase().trim();
                    String issn = Utils.getStrField(entry, "issn").toLowerCase().trim();
                    String eissn = Utils.getStrField(entry, "eissn").toLowerCase().trim();
                    Set<String> set = new HashSet<>();
                    if (full.length() > 2) {
                        set.add(abbr);
                    }
                    if (abbr.length() > 2) {
                        set.add(abbr);
                    }
                    if (issn.length() == 9 && issn.charAt(4) == '-' && !issn.contains("*")) {
                        set.add(issn);
                    }
                    if (eissn.length() == 9 && eissn.charAt(4) == '-' && !eissn.contains("*")) {
                        set.add(eissn);
                    }
                    if (!set.isEmpty()) {
                        equalSet.add(CollUtil.join(set, spl));
                    }
                }
            }
        }
    }

    public static void getDownWosTable(String jcrJour2024Table, String jcrJourDetailTable, String utArticleTable, String downWosTable) throws SQLException {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        System.out.println(format.format(new Date()) + " 合并期刊开始了~~~");
        Set<String> equalSet = new HashSet<>();

        String limit = "";
//        String limit = " limit 1";
        readJcrJourDetailTable(jcrJourDetailTable, "id", "full_journal_title`,`jcr_abbreviated_title`,`issn", equalSet, limit);
        System.out.println(format.format(new Date()) + " 表：" + jcrJourDetailTable + " equalSet.size(): " + equalSet.size());

        readJcrJour2024Table(jcrJour2024Table, "id", "journal_name`,`abbr_journal`,`issn`,`eissn", equalSet, limit);
        System.out.println(format.format(new Date()) + " 表：" + jcrJour2024Table + " equalSet.size(): " + equalSet.size());

        readUtArticleTable(utArticleTable, "pid", "J9`,`SN`,`EI", equalSet, limit);
        System.out.println(format.format(new Date()) + " 表：" + utArticleTable + " equalSet.size(): " + equalSet.size());

        insertDownWosTable(utArticleTable, "pid", "J9`,`SN`,`EI`,`PY", equalSet, downWosTable, limit);
        System.out.println(format.format(new Date()) + " 表：" + downWosTable + " equalSet.size(): " + equalSet.size());
    }


}
